'''
执行过一次测试用例后，就不是原来的测试环境了，目的要还原测试环境
1）删除上一次添加的所有数据 删除所有操作
2）提前添加一些信息，添加数据操作
3）删除单行操作
'''
import pymysql

from auto_uitest.ranzhiUitest.caw.logger import Logger


def myConn(db_info):
    try:
        conn = pymysql.connect(user=db_info["user"],
                               password=db_info["pwd"],
                               database=db_info["dbname"],
                               host=db_info["host"],
                               port=db_info["port"],
                               charset=db_info["charset"]
                               )

        logger = Logger()
        logger.info(f"数据库连接成功")
        return conn
    except Exception as e:
        logger = Logger()
        logger.error("-----数据库链接失败------")


def myexecute(conn,sql):
    try:
        # 通过链接对象可以获取游标
        cursor1 = conn.cursor()

        # 通过游标执行数据库命令
        # cursor1.execute("select * from user")  执行查询命令
        # 执行修改命令
        cursor1.execute(sql)
        conn.commit()

        # 查询结果
        rall = cursor1.fetchall() # 返回所有数据
        # r = cursor1.fetchone() # 返回其中一条数据
        print(rall)
    except Exception:
        print("---数据库执行失败---")

def myclose(conn):
    try:
        # 链接关闭
        conn.close()
    except Exception:
        print("----数据库关闭失败----")

# 删除所有操作 除了admin
def deletAllusers(db_info):
    conn = myConn(db_info)
    sql = f"delete from sys_user where account != 'admin'"
    myexecute(conn,sql)
    print("-------删除成功------")
    myclose(conn)

# 删除单个数据
def deletoneuser(db_info,username):
    conn = myConn(db_info)
    sql = f"delete from sys_user where account = '{username}'"
    myexecute(conn, sql)
    myclose(conn)

# 添加数据，只需要添加用户名和邮箱即可
def adduser(db_info,username,email):
    conn = myConn(db_info)
    sql = (f"INSERT INTO `ranzhi`.`sys_user` (`dept`, `account`, `password`, `realname`, `role`, `nickname`, `admin`, `avatar`, `birthday`, `gender`, `email`, `skype`, `qq`, `yahoo`, `gtalk`, `wangwang`, `site`, `mobile`, `phone`, `address`, `zipcode`, `visits`, `ip`, `last`, `ping`, `fails`, `join`, `locked`, `deleted`, `status`) "
           f"VALUES (0, '{username}', 'c7122a1349c22cb3c009da3613d242ab', 'admin', '', '', 'super', '', '2000-01-01', 'u', '{email}', '', '', '', '', '', '', '', '', '', '', 199, '127.0.0.1', '2025-09-02 09:41:58', '2025-09-02 09:42:00', 0, '2025-08-01 15:21:34', '2000-01-01 01:01:01', '0', 'offline');")

    myexecute(conn, sql)
    myclose(conn)


if __name__ == '__main__':
    db_info ={"user":"root","pwd":"!QAZ2wsx","host":"192.168.79.129","port":3306,"dbname":"ranzhi","charset":"utf8"}
    # deletAllusers(db_info)
    adduser(db_info,"admn1","aksg@124.com")

